package dao

import (
	"fmt"
	"time"
	"yundian/internel/app/helper/request"
	"yundian/internel/pkg/dingo"
	"yundian/internel/pkg/dingo/global"
)

type StatisticalScreenDao struct {
}

func NewStatisticalScreenDao() *StatisticalScreenDao {
	return &StatisticalScreenDao{}
}

const multipleNum = 3   // 倍数
const dayLenNum = 20000 // 查询优化配置

// GetPlacesInfo 查询网点/设备数据
func (d *StatisticalScreenDao) GetPlacesInfo() *dingo.ErrorWrap {
	dbInfo := &request.ScreenGetGetPlacesInfoRes{}
	sql := `SELECT SUM(d_num)d_num,COUNT(1)p_num FROM (SELECT SUM(1)d_num,p.id FROM places p 
	LEFT JOIN devices d ON p.id=d.place_id WHERE p.id>0 AND d.state=2 GROUP BY p.id)d`
	db := global.Orm.Raw(sql).Find(&dbInfo)
	dbInfo.PlaceNum = dbInfo.PlaceNum * multipleNum
	dbInfo.DeviceNum = dbInfo.DeviceNum * multipleNum

	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetPlacesTypeRatio 门店占比查询
func (d *StatisticalScreenDao) GetPlacesTypeRatio() *dingo.ErrorWrap {
	var dbList []*request.ScreenGetPlacesTypeRatioRes
	fields := fmt.Sprintf("COUNT(1)*%d num,", multipleNum)
	db := global.Orm.Table("places p").Select(fields + "IFNULL(kp.ka_name,'其它')ka_name")
	db.Joins("LEFT JOIN ka_project kp ON kp.ka_label=p.project_label")
	db.Group("ka_name").Find(&dbList)

	return dingo.ErrorIf(db.Error, dbList)
}

// GetPlacesGeography 门店地理分布查询
func (d *StatisticalScreenDao) GetPlacesGeography() *dingo.ErrorWrap {
	var dbList []*request.ScreenGetPlacesGeographyRes
	fields := fmt.Sprintf(`COUNT(1)*%d num,IFNULL(full_name,
	IF(p.province<>'',p.province,'其它'))r_city,ltr.lat,ltr.lng`, multipleNum)
	db := global.Orm.Table("places p").Select(fields)
	db.Joins("LEFT JOIN lc_tencent_regions ltr ON p.city_id=ltr.id")
	db.Group("r_city").Find(&dbList)

	return dingo.ErrorIf(db.Error, dbList)
}

// GetPayMonthChart 总流水统计
func (d *StatisticalScreenDao) GetPayMonthChart(params *request.ScreenGetPayMonthChartReq) *dingo.ErrorWrap {
	var dbList []*request.ScreenGetPayMonthChartRes
	fields := fmt.Sprintf("SUM(settlement_money)*%d money,", multipleNum)
	db := global.Orm.Table("orders").Select(fields + "DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m')t")
	db.Where("state = ?", 5)
	db.Group("t").Find(&dbList)

	return dingo.ErrorIf(db.Error, dbList)
}

// GetStatisticsOrderList 查询最新订单列表
func (d *StatisticalScreenDao) GetStatisticsOrderList(params *request.ScreenGetOrderListReq) *dingo.ErrorWrap {
	maxId := 0
	var dbList []*request.ScreenGetOrderListRes

	if params.Id == 0 {
		maxId = d.GetStatisticsMaxOrderId().Unwrap().(int)
	}

	fields := `id,money,place_name,DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d %T') create_time`
	db := global.Orm.Table("orders").Select(fields)

	if maxId > dayLenNum {
		db.Where("id>=?", maxId-dayLenNum)
	}
	if params.Id > 0 {
		db.Where("id>?", params.Id)
	}
	db.Where("DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d')=?", time.Now().Format("2006-01-02"))
	db.Order("id DESC").Limit(100).Find(&dbList)

	return dingo.ErrorIf(db.Error, dbList)
}

// GetStatisticsInfoTodayEndMoney 查询今日完结订单流水
func (d *StatisticalScreenDao) GetStatisticsInfoTodayEndMoney(maxId int) *dingo.ErrorWrap {
	dbInfo := &request.ScreenStatisticalDayInfoRes{}
	db := global.Orm.Table("orders")
	db.Select(fmt.Sprintf("SUM(money)*%d pay_money,COUNT(1)*%d order_num", multipleNum, multipleNum))

	if maxId > dayLenNum*5 {
		db.Where("id>=?", maxId-dayLenNum*5)
	}
	db.Where("DATE_FORMAT(FROM_UNIXTIME(end_at), '%Y-%m-%d')=?", time.Now().Format("2006-01-02"))
	db.Where("state=?", 5)
	db.Find(&dbInfo)

	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetStatisticsInfoTodayCreateOrderNum 查询今日创建订单数
func (d *StatisticalScreenDao) GetStatisticsInfoTodayCreateOrderNum(maxId int) *dingo.ErrorWrap {
	dbInfo := &request.ScreenStatisticalDayInfoRes{}

	db := global.Orm.Table("orders")
	db.Select(fmt.Sprintf("SUM(money)*%d pay_money,COUNT(1)*%d order_num", multipleNum, multipleNum))

	if maxId > dayLenNum {
		db.Where("id>=?", maxId-dayLenNum)
	}
	db.Where("DATE_FORMAT(FROM_UNIXTIME(create_at), '%Y-%m-%d')=?", time.Now().Format("2006-01-02"))
	db.Where("state IN ?", []int{2, 5})
	db.Find(&dbInfo)

	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetStatisticsMaxOrderId 查询订单最大ID
func (d *StatisticalScreenDao) GetStatisticsMaxOrderId() *dingo.ErrorWrap {
	maxId := 0
	db := global.Orm.Table("orders").Select("MAX(id)").Find(&maxId)

	return dingo.ErrorIf(db.Error, maxId)
}
